package com.example.myapplication.db;

import android.annotation.SuppressLint;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import com.example.myapplication.model.Course;
import com.example.myapplication.model.Review;
import com.example.myapplication.model.Teacher;
import org.json.JSONArray;
import org.json.JSONObject;
import java.util.ArrayList;

public class DBManager {
    private DBHelper dbHelper;

    public DBManager(Context context) {
        dbHelper = new DBHelper(context);
    } // 查询开设某课程的所有老师

    public ArrayList<Teacher> getTeachersByCourseName(String courseName) {
        ArrayList<Teacher> list = new ArrayList<>();
        SQLiteDatabase db = null;
        Cursor c = null;
        try {
            db = dbHelper.getReadableDatabase();
            c = db.rawQuery(
                    "SELECT DISTINCT teacher.name, teacher.info FROM course JOIN teacher ON course.teacher_id = teacher.id WHERE course.name LIKE ?",
                    new String[] { "%" + courseName + "%" });
            while (c != null && c.moveToNext()) {
                list.add(new Teacher(c.getString(0), c.getString(1)));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (c != null) {
                c.close();
            }
            if (db != null) {
                db.close();
            }
        }
        return list;
    }

    // 查询某老师开设的所有课程
    public ArrayList<Course> getCoursesByTeacherName(String teacherName) {
        ArrayList<Course> list = new ArrayList<>();
        SQLiteDatabase db = null;
        Cursor c = null;
        try {
            db = dbHelper.getReadableDatabase();
            c = db.rawQuery(
                    "SELECT DISTINCT c.name, t.name, " +
                            "(SELECT AVG(CAST(rating AS FLOAT)) FROM review r JOIN course co ON r.course_id = co.id WHERE co.name = c.name) as avg_rating "
                            +
                            "FROM course c JOIN teacher t ON c.teacher_id = t.id WHERE t.name LIKE ?",
                    new String[] { "%" + teacherName + "%" });
            while (c != null && c.moveToNext()) {
                float avgRating = c.isNull(2) ? 0 : c.getFloat(2);
                list.add(new Course(c.getString(0), c.getString(1), avgRating));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (c != null) {
                c.close();
            }
            if (db != null) {
                db.close();
            }
        }
        return list;
    }

    public ArrayList<Course> getCoursesByNameOrTeacher(String query) {
        ArrayList<Course> list = new ArrayList<>();
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        Cursor c = db.rawQuery(
                "SELECT course.id, course.name, teacher.name FROM course JOIN teacher ON course.teacher_id = teacher.id WHERE course.name LIKE ? OR teacher.name LIKE ?",
                new String[] { "%" + query + "%", "%" + query + "%" });
        while (c.moveToNext()) {
            list.add(new Course(c.getString(1), c.getString(2)));
        }
        c.close();
        db.close();
        return list;
    }

    public Teacher getTeacherByCourseName(String courseName) {
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        Cursor c = db.rawQuery(
                "SELECT teacher.name, teacher.info FROM course JOIN teacher ON course.teacher_id = teacher.id WHERE course.name = ?",
                new String[] { courseName });
        Teacher teacher = null;
        if (c.moveToFirst()) {
            teacher = new Teacher(c.getString(0), c.getString(1));
        }
        c.close();
        db.close();
        return teacher;
    }

    // 评论排序方式枚举
    public enum ReviewSortType {
        TIME_DESC,    // 按时间降序（最新的在前）
        TIME_ASC,     // 按时间升序（最早的在前）
        LIKES_DESC,   // 按点赞数降序（最多的在前）
        LIKES_ASC     // 按点赞数升序（最少的在前）
    }

    public ArrayList<Review> getReviewsByCourseName(String courseName, ReviewSortType sortType) {
        ArrayList<Review> list = new ArrayList<>();
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        
        String orderBy;
        switch (sortType) {
            case TIME_ASC:
                orderBy = "review.time ASC";
                break;
            case LIKES_DESC:
                orderBy = "review.like_count DESC, review.time DESC";
                break;
            case LIKES_ASC:
                orderBy = "review.like_count ASC, review.time DESC";
                break;
            case TIME_DESC:
            default:
                orderBy = "review.time DESC";
                break;
        }
        
        Cursor c = db.rawQuery(
                "SELECT review.reviewer, review.rating, review.comment, review.time, review.like_count " +
                "FROM review JOIN course ON review.course_id = course.id " +
                "WHERE course.name = ? ORDER BY " + orderBy,
                new String[] { courseName });
                
        while (c.moveToNext()) {
            list.add(new Review(
                    c.getString(0), // reviewer
                    c.getInt(1),    // rating
                    c.getString(2), // comment
                    c.getString(3), // time
                    c.getInt(4)     // like_count
            ));
        }
        c.close();
        db.close();
        return list;
    }

    // 为了保持向后兼容，保留原方法
    public ArrayList<Review> getReviewsByCourseName(String courseName) {
        return getReviewsByCourseName(courseName, ReviewSortType.TIME_DESC);
    }

    public void addReview(String courseName, String reviewer, int rating, String comment) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        Cursor c = db.rawQuery("SELECT id FROM course WHERE name = ?", new String[] { courseName });
        int courseId = -1;
        if (c.moveToFirst()) {
            courseId = c.getInt(0);
        }
        c.close();
        if (courseId != -1) {
            // 写入当前时间，格式 yyyy-MM-dd
            String time = new java.text.SimpleDateFormat("yyyy-MM-dd").format(new java.util.Date());
            db.execSQL(
                    "INSERT INTO review (course_id, reviewer, rating, comment, time, like_count) VALUES (?, ?, ?, ?, ?, 0)",
                    new Object[] { courseId, reviewer, rating, comment, time });
        }
        db.close();
    } // 点赞+1并返回新值

    public int addLikeToReview(String reviewer, String comment, String time) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        // 通过 reviewer+comment+time 唯一定位一条评论
        Cursor c = db.rawQuery("SELECT id, like_count FROM review WHERE reviewer=? AND comment=? AND time=?",
                new String[] { reviewer, comment, time });
        int newLikeCount = -1;
        if (c.moveToFirst()) {
            int id = c.getInt(0);
            int likeCount = c.getInt(1) + 1;
            db.execSQL("UPDATE review SET like_count=? WHERE id=?", new Object[] { likeCount, id });
            newLikeCount = likeCount;
        }
        c.close();
        db.close();
        return newLikeCount;
    }

    // 添加老师和课程信息
    public boolean addTeacherAndCourse(String teacherName, String teacherInfo, String courseName) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        try {
            db.beginTransaction();

            // 1. 检查老师是否已存在
            Cursor c = db.rawQuery("SELECT id FROM teacher WHERE name = ?", new String[] { teacherName });
            long teacherId;
            if (c.moveToFirst()) {
                teacherId = c.getLong(0);
                // 更新老师信息
                db.execSQL("UPDATE teacher SET info = ? WHERE id = ?",
                        new Object[] { teacherInfo, teacherId });
            } else {
                // 插入新老师
                db.execSQL("INSERT INTO teacher (name, info) VALUES (?, ?)",
                        new Object[] { teacherName, teacherInfo });
                c = db.rawQuery("SELECT last_insert_rowid()", null);
                c.moveToFirst();
                teacherId = c.getLong(0);
            }
            c.close();

            // 2. 添加课程（如果不存在）
            c = db.rawQuery(
                    "SELECT id FROM course WHERE name = ? AND teacher_id = ?",
                    new String[] { courseName, String.valueOf(teacherId) });
            if (!c.moveToFirst()) {
                db.execSQL(
                        "INSERT INTO course (name, teacher_id) VALUES (?, ?)",
                        new Object[] { courseName, teacherId });
            }
            c.close();

            db.setTransactionSuccessful();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            try {
                db.endTransaction();
            } catch (Exception e) {
                e.printStackTrace();
            }
            db.close();
        }
    }

    // 检查数据库状态
    public void checkDatabaseState() {
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        try {
            // 检查教师表
            Cursor c = db.rawQuery("SELECT COUNT(*) FROM teacher", null);
            c.moveToFirst();
            int teacherCount = c.getInt(0);
            c.close();

            // 检查课程表
            c = db.rawQuery("SELECT COUNT(*) FROM course", null);
            c.moveToFirst();
            int courseCount = c.getInt(0);
            c.close();

            System.out.println("数据库状态: 教师数=" + teacherCount + ", 课程数=" + courseCount);
            
            // 输出所有教师
            c = db.rawQuery("SELECT name FROM teacher", null);
            System.out.println("教师列表:");
            while(c.moveToNext()) {
                System.out.println("- " + c.getString(0));
            }
            c.close();
            
            // 输出所有课程
            c = db.rawQuery("SELECT c.name, t.name FROM course c JOIN teacher t ON c.teacher_id = t.id", null);
            System.out.println("课程列表:");
            while(c.moveToNext()) {
                System.out.println("- " + c.getString(0) + " (教师: " + c.getString(1) + ")");
            }
            c.close();
            
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("数据库检查失败: " + e.getMessage());
        } finally {
            db.close();
        }
    }

    // 导出数据库数据为JSON字符串
    @SuppressLint("Range")
    public String exportData() {
        JSONObject data = new JSONObject();
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        
        try {
            // 导出教师数据
            JSONArray teachers = new JSONArray();
            Cursor c = db.rawQuery("SELECT * FROM teacher", null);
            while (c.moveToNext()) {
                JSONObject teacher = new JSONObject();
                teacher.put("id", c.getInt(c.getColumnIndex("id")));
                teacher.put("name", c.getString(c.getColumnIndex("name")));
                teacher.put("info", c.getString(c.getColumnIndex("info")));
                teachers.put(teacher);
            }
            c.close();
            data.put("teachers", teachers);

            // 导出课程数据
            JSONArray courses = new JSONArray();
            c = db.rawQuery("SELECT * FROM course", null);
            while (c.moveToNext()) {
                JSONObject course = new JSONObject();
                course.put("id", c.getInt(c.getColumnIndex("id")));
                course.put("name", c.getString(c.getColumnIndex("name")));
                course.put("teacher_id", c.getInt(c.getColumnIndex("teacher_id")));
                course.put("info", c.getString(c.getColumnIndex("info")));
                courses.put(course);
            }
            c.close();
            data.put("courses", courses);

            // 导出评论数据
            JSONArray reviews = new JSONArray();
            c = db.rawQuery("SELECT * FROM review", null);
            while (c.moveToNext()) {
                JSONObject review = new JSONObject();
                review.put("id", c.getInt(c.getColumnIndex("id")));
                review.put("course_id", c.getInt(c.getColumnIndex("course_id")));
                review.put("reviewer", c.getString(c.getColumnIndex("reviewer")));
                review.put("rating", c.getInt(c.getColumnIndex("rating")));
                review.put("comment", c.getString(c.getColumnIndex("comment")));
                review.put("time", c.getString(c.getColumnIndex("time")));
                review.put("like_count", c.getInt(c.getColumnIndex("like_count")));
                reviews.put(review);
            }
            c.close();
            data.put("reviews", reviews);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            db.close();
        }
        
        return data.toString();
    }

    // 从JSON字符串导入数据
    public boolean importData(String jsonData) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        
        try {
            db.beginTransaction();
            
            // 清空现有数据
            db.execSQL("DELETE FROM review");
            db.execSQL("DELETE FROM course");
            db.execSQL("DELETE FROM teacher");
            
            JSONObject data = new JSONObject(jsonData);
            
            // 导入教师数据
            JSONArray teachers = data.getJSONArray("teachers");
            for (int i = 0; i < teachers.length(); i++) {
                JSONObject teacher = teachers.getJSONObject(i);
                db.execSQL("INSERT INTO teacher (id, name, info) VALUES (?, ?, ?)",
                        new Object[]{
                                teacher.getInt("id"),
                                teacher.getString("name"),
                                teacher.getString("info")
                        });
            }
            
            // 导入课程数据
            JSONArray courses = data.getJSONArray("courses");
            for (int i = 0; i < courses.length(); i++) {
                JSONObject course = courses.getJSONObject(i);
                db.execSQL("INSERT INTO course (id, name, teacher_id, info) VALUES (?, ?, ?, ?)",
                        new Object[]{
                                course.getInt("id"),
                                course.getString("name"),
                                course.getInt("teacher_id"),
                                course.getString("info")
                        });
            }
            
            // 导入评论数据
            JSONArray reviews = data.getJSONArray("reviews");
            for (int i = 0; i < reviews.length(); i++) {
                JSONObject review = reviews.getJSONObject(i);
                db.execSQL("INSERT INTO review (id, course_id, reviewer, rating, comment, time, like_count) VALUES (?, ?, ?, ?, ?, ?, ?)",
                        new Object[]{
                                review.getInt("id"),
                                review.getInt("course_id"),
                                review.getString("reviewer"),
                                review.getInt("rating"),
                                review.getString("comment"),
                                review.getString("time"),
                                review.getInt("like_count")
                        });
            }
            
            db.setTransactionSuccessful();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            try {
                db.endTransaction();
            } catch (Exception e) {
                e.printStackTrace();
            }
            db.close();
        }
    }
}
